<?php
class Reports extends CI_Controller {

	var $page = "reports";
	var $message = '';

	var $action  = '';

	public function index(){
			
		$userId  = $this->session->userdata('user_id');
			
		$usertype = $this->session->userdata('user_type');

		if(isset($_REQUEST['download'])){
			$this->generateExportReport($_REQUEST['id']);
		}

			
		if($usertype != 'A'){
			$query = $this->db->query("select r.*,
                  (select concat(firstname,' ',lastname) from users u where u.id = r.updatedby) as updatedby,
		          (select concat(firstname,' ',lastname) from users u where u.id = r.createdby) as createdby		
				    from reports as r
				    where r.valid = 1 and r.createdby = '".$userId."'");
		}
		else
		$query = $this->db->query("select r.*,
                  (select concat(firstname,' ',lastname) from users u where u.id = r.updatedby) as updatedby,
		          (select concat(firstname,' ',lastname) from users u where u.id = r.createdby) as createdby		
				    from reports as r
				    where r.valid = 1 ");
		//$data['User'] = "meeee";
		//echo "SET.......".$this->session->userdata('session_id');
		if($query->num_rows() > 0)
		$data['totalRecords'] =  $query->num_rows();
		else
		$data['totalRecords'] = 0;

		if($this->message != '')
		$data['successMessage'] = $this->message;
			
			
		$this->admin($data,$query);
	}

	public function add(){
		$this->action = 'add';
			

		$data['shippers'] =  $this->db->query("select * from shipper where valid = 1");
			

		$this->admin($data,null);
	}



	public function save(){

		$from = $_REQUEST['weekFrom'];
		$to = $_REQUEST['weekTo'];
			
		//echo "SAVE :: $from --- $to";
		$description = $_REQUEST['description'];

		$isUpdate = false;
			

		$shipper = $_REQUEST['shipper'];
								   						$volume  = $_REQUEST['volume'];
								   						$commodity = $_REQUEST['commodity'];
								   						$destination = $_REQUEST['destination'];
								   						$vessel = $_REQUEST['vessel'];
								   						$carrier = $_REQUEST['carrier'];
								   						$lct = $_REQUEST['lct'];
								   						$atd = $_REQUEST['atd'];
								   						$comment = $_REQUEST['comment'];
								   						$status1 = $_REQUEST['status1'];
								   						$status2 = $_REQUEST['status2'];
								   						$status3 = $_REQUEST['status3'];
								   						$status4 = $_REQUEST['status4'];
								   						$status5 = $_REQUEST['status5'];
								   						$status6 = $_REQUEST['status6'];
								   						$status7 = $_REQUEST['status7'];
								   						$status8 = $_REQUEST['status8'];
								   						$status9 = $_REQUEST['status9'];
								   						$status10 = $_REQUEST['status10'];
								   						$status11 = $_REQUEST['status11'];
								   						$status12 = $_REQUEST['status12'];
								   							
								   						$CURRENTIDFORREPORT = null;

								   						if(isset($_REQUEST['id'])){
								   							$isUpdate  = true;
								   							//$CURRENTIDFORREPORT = $_REQUEST['id'];
								   						}

								   							
								   						if($isUpdate){


								   							$query = $this->db->query("UPDATE  reports SET  weekFrom =  '$from' , weekTo = '$to',description =  '$description' , updatedon =  NOW() WHERE  id =".$_REQUEST['id']);

								   							$this->message = "Export Repport was successfully Updated.";

								   							$count = 0;
								   							if(isset($_REQUEST['reportItemId'])){
								   								$reportItemId = $_REQUEST['reportItemId'];
								   									
								   									
								   								foreach($reportItemId as $repItemId){

								   									$this->saveToExportReportItems($_REQUEST['id'] , true , $repItemId , $shipper[$count],
								   									$volume[$count],$commodity[$count],$destination[$count],$vessel[$count],$carrier[$count],$lct[$count],$atd[$count],$comment[$count],$status1[$count],$status2[$count],$status3[$count],
 												   									$status4[$count],$status5[$count],$status6[$count],$status7[$count],$status8[$count],$status9[$count],$status10[$count],$status11[$count],$status12[$count]);

 												   									$count++;
								   								}

								   							}
								   							$size = count($shipper);

								   							for( ; $count < $size ; $count++){
								   									
								   								$this->saveToExportReportItems($_REQUEST['id'] , false , null , $shipper[$count],
								   								$volume[$count],$commodity[$count],$destination[$count],$vessel[$count],$carrier[$count],$lct[$count],$atd[$count],$comment[$count],$status1[$count],$status2[$count],$status3[$count],
								   								$status4[$count],$status5[$count],$status6[$count],$status7[$count],$status8[$count],$status9[$count],$status10[$count],$status11[$count],$status12[$count]);
								   									
								   							}

								   							$this->createLog('REPORTS' , 'UPDATED' ,  "Updated Export Report Record where id = ".$_REQUEST['id']);


								   						}else{
								   							$this->db->query("INSERT INTO  reports (id ,weekFrom , weekTo ,description".$this->creationFields().")
								  VALUES (NULL ,  '$from', '$to',  '$description'".$this->creationValues().")");

								   							//	$CURRENTIDFORREPORT = $this->db->insert_id();

								   							$count = 0;
								   							foreach($shipper as $ship){
								   									
								   								$this->saveToExportReportItems($this->db->insert_id() , false , null , $ship,
								   								$volume[$count],$commodity[$count],$destination[$count],$vessel[$count],$carrier[$count],$lct[$count],$atd[$count],$comment[$count],$status1[$count],$status2[$count],$status3[$count],
								   								$status4[$count],$status5[$count],$status6[$count],$status7[$count],$status8[$count],$status9[$count],$status10[$count],$status11[$count],$status12[$count]);
								   									
								   								$count++;

								   							}



								   							$this->createLog('REPORTS' , 'CREATED' ,  "Created new Report");

								   							$this->message = "New Report was successfully Created.";
								   						}
								   							
								   						$this->index();

	}

	public function delete($id){

		$data['deleteId'] = $id;
			
		$query = $this->db->query("update reports set valid = false  where id = $id limit 1" );
			
		$this->createLog('REPORTS' , 'DELETED' ,  "Deleted Report Record where id = $id");
			
		$this->index();
			
	}

	public function edit($id){
			
		//$this->page = 'edit'.$this->page;
			
		$this->action = 'edit';
			
		//echo "select * from reports where id = $id and valid= 1 limit 1" ;
			
		$query = $this->db->query("select * from reports where id = $id and valid= 1 limit 1" );
		//$row =
		$data['report'] = $query->row();

		$data['shippers'] =  $this->db->query("select * from shipper where valid = 1");
			
		$data['exportReportItems'] =  $this->db->query("select * from exportreportitem where reportsId = $id and  valid = 1");
			
			
		$this->admin($data,$query);
			
	}


	public function saveToExportReportItems($reportsId   , $isUpdate , $reportItemId , $shipper, $volume ,$commodity ,$destination ,$vessel ,$carrier ,$lct ,$atd ,$comment ,$status1 ,$status2 ,$status3 ,
	$status4 ,$status5 ,$status6 ,$status7 ,$status8 ,$status9 ,$status10 ,$status11 ,$status12){
			

		$record =  array('shipper' => $shipper , 'volume' => $volume,'commodity' => $commodity, 'destination' => $destination ,
							'vessel' => $vessel, 'carrier' => $carrier, 'lct' => $lct ,
							'atd' => $atd, 'comment' => $comment, 'status1' => $status1 ,
							'status2' => $status2, 'status3' => $status3, 'status4' => $status4 ,
							'status5' => $status5, 'status6' => $status6, 'status7' => $status7 ,
							'status8' => $status8, 'status9' => $status9, 'status10' => $status10 ,
							'status11' => $status11, 'status12' => $status12 , 'reportsId' => $reportsId ,
							'updatedon' => "NOW()", 'createdon' => "NOW()" , 'valid' => true
		);

		if($isUpdate){

			$where = " valid= 1 and reportsid = $reportsId and id = $reportItemId";

			$str = $this->db->update_string('exportreportitem', $record , $where);

		}else{

			$str = $this->db->insert_string('exportreportitem', $record);

			//echo "STR -- > ". $str;

		}
		$this->db->query($str);
	}

	function generateExportReport($id){
		//ECHO
			
		//load our new PHPExcel library
		$this->load->library('excel');
		//activate worksheet number 1
		$this->excel->setActiveSheetIndex(0);

		$activeSheet = $this->excel->getActiveSheet();

		//name the worksheet
		$activeSheet->setTitle('Skybest');
		//set cell A1 content with some text
		//$this->excel->getActiveSheet()->setCellValue('A1', 'This is just some text value');

		//change the font size
		//$activeSheet->getStyle('A1')->getFont()->setSize(10);
		//make the font become bold
		//$this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
		//FOR HEADER ::
		$activeSheet->setCellValue('B1', $this->session->userdata('user_fullname'));
		$activeSheet->setCellValue('A1' , 'Prepared By');
		$activeSheet->getStyle('A1')->getFont()->setBold(true);
		$activeSheet->setCellValue('A2', 'Customer Service - Report (Sky Best)');
		$activeSheet->getStyle('A2')->getFont()->setBold(true);
		//$activeSheet->mergeCells('A3:B3');
		$activeSheet->setCellValue('A3', 'WEEK');
		$activeSheet->getStyle('A3')->getFont()->setBold(true);
		//$activeSheet->mergeCells('C3:D3');
		$activeSheet->setCellValue('B3', 'START DAY WEEK');
		//$activeSheet->mergeCells('E3:F3');
		$activeSheet->setCellValue('C3', 'END DAY WEEK');

		for($atoz=65 ; $atoz < 65 + 26 ;$atoz++)
		{
			//	echo chr($atoz)."----";
			$activeSheet->getColumnDimension(chr($atoz))->setWidth(30);
			if($atoz > 65)
			$activeSheet->getStyle(chr($atoz))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

		}


		$activeSheet->setCellValue('A6', 'SHIPPER');
		$activeSheet->getStyle('A6')->getFont()->setBold(true);



		$activeSheet->setCellValue('A7', 'Volume');
		$activeSheet->setCellValue('A8', 'Commodity');
		$activeSheet->setCellValue('A9', 'Destination');
		$activeSheet->setCellValue('A10', 'Vessel');
		$activeSheet->setCellValue('A11', 'Carrier');
		$activeSheet->setCellValue('A12', 'LCT');
		$activeSheet->setCellValue('A13', 'ATD');


		$activeSheet->setCellValue('A16', 'STATUS');
		$activeSheet->getStyle('A16')->getFont()->setBold(true);


		$activeSheet->setCellValue('A17', 'Booking Confirmation');
		$activeSheet->setCellValue('A18', 'Check ATW received');
		$activeSheet->setCellValue('A19', 'Forward ATW to Shipper');
		$activeSheet->setCellValue('A20', 'Update/Monitor Shipper');
		$activeSheet->setCellValue('A21', 'Follow-up final SI to Shipper');
		$activeSheet->setCellValue('A22', 'Received final SI from Shipper');
		$activeSheet->setCellValue('A23', 'Send Final pro-forma to Carrier');
		$activeSheet->setCellValue('A24', 'Received B/L Copy from Carrier');
		$activeSheet->setCellValue('A25', 'Encode Billing in BCIS');
		$activeSheet->setCellValue('A26', 'Request for Telex Release');
		$activeSheet->setCellValue('A27', 'Request Payment fo Carrier');
		$activeSheet->setCellValue('A28', 'Revised B/L');
		$activeSheet->setCellValue('A29', 'COMMENT');





		$exportReportItems =  $this->db->query("select * from exportreportitem where reportsId = $id and  valid = 1");
			
		$numberColumn = 66;

		forEach($exportReportItems->result() as $reportItem){

			$column = chr($numberColumn);

			$activeSheet->setCellValue($column.'6', $reportItem->shipper);
			$activeSheet->getStyle($column.'6')->getFont()->setBold(true);
			$activeSheet->setCellValue($column.'7', $reportItem->volume);
			$activeSheet->setCellValue($column.'8', $reportItem->commodity);
			$activeSheet->setCellValue($column.'9', $reportItem->destination);
			$activeSheet->setCellValue($column.'10', $reportItem->vessel);
			$activeSheet->setCellValue($column.'11', $reportItem->carrier);
			$activeSheet->setCellValue($column.'12', $reportItem->lct);
			$activeSheet->setCellValue($column.'13', $reportItem->atd);


			$activeSheet->setCellValue($column.'16', 'Date Completed');
			$activeSheet->getStyle($column.'16')->getFont()->setBold(true);
			$activeSheet->getStyle($column.'16')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);



			$activeSheet->setCellValue($column.'17', $reportItem->status1);
			$activeSheet->setCellValue($column.'18', $reportItem->status2);
			$activeSheet->setCellValue($column.'19', $reportItem->status3);
			$activeSheet->setCellValue($column.'20',$reportItem->status4);
			$activeSheet->setCellValue($column.'21', $reportItem->status5);
			$activeSheet->setCellValue($column.'22', $reportItem->status6);
			$activeSheet->setCellValue($column.'23', $reportItem->status7);
			$activeSheet->setCellValue($column.'24', $reportItem->status8);
			$activeSheet->setCellValue($column.'25', $reportItem->status9);
			$activeSheet->setCellValue($column.'26', $reportItem->status10);
			$activeSheet->setCellValue($column.'27', $reportItem->status11);
			$activeSheet->setCellValue($column.'28', $reportItem->status12);
			$activeSheet->setCellValue($column.'29', $reportItem->comment);

			$numberColumn++;

		}

		//set aligment to center for that merged cell (A1 to D1)
		//$this->excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			
		$filename='skybest.xls'; //save our workbook as this file name
		header('Content-Type: application/vnd.ms-excel'); //mime type
		header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
		header('Cache-Control: max-age=0'); //no cache

		//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension)
		//if you want to save it as .XLSX Excel 2007 format
		$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
		//force user to download the Excel file without writing it to server's HD
		$objWriter->save('php://output');
			
			
	}





}




